﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Data.SQLite;
using System.Threading;
using Mark.Sqlite.Helper;

namespace Mark.Sqlite
{
	public class SqliteContext : TableMapper, IDisposable
	{
		readonly SQLiteConnection _connection;
		SQLiteTransaction _transaction;


		public SqliteContext(SQLiteConnection connection)
		{
			_connection = connection;
		}


		public SqliteContext(string connectionString)
		{
			_connection = new SQLiteConnection(connectionString);
		}


		public int Insert<T>(T entity) where T : class, new()
		{
			return Insert(entity, false);
		}


		public int InsertOrReplace<T>(T entity) where T : class, new()
		{
			return Insert(entity, true);
		}


		int Insert<T>(T entity, bool executeInsertOrReplace) where T : class, new()
		{
			var parameters = new HashSet<SQLiteParameter>();
			var getLastIncrement = false;

			//MAPEAR TABELA
			var mapInfo = MapEntity(entity);

			//GERAR STRING DOS CAMPOS
			var cmdColumns = new StringBuilder();
			var cmdParameters = new StringBuilder();

			foreach (var columnInfo in mapInfo.Columns)
			{
				var value = columnInfo.PropertyInfo.GetValue(entity, null) ?? DBNull.Value;

				// QUANDO O CAMPO É AUTO INCREMENTO, E ELE ESTA SEM VALOR, NAO DEVE PARTICIPAR DO INSERT, POR ISSO ENTRAMOS NO "CONTINUE"
				if (columnInfo.IsAutoIncrement)
				{
					if (value == DBNull.Value || Convert.ToInt32(value) <= 0)
					{
						getLastIncrement = true;
						continue;
					}
				}

				//SOCRIAR COMANDO TEXTO, SO O MESMO NAO FOI CRIADO ANTERIORMENTE, CRIAR OS COMANDOS EM TEXTO
				cmdColumns.Append(string.Format("{0}, ", columnInfo.ColumnName));
				cmdParameters.Append(string.Format("@{0}, ", columnInfo.ColumnName));

				//ADD OS PARAEMTROS, SE FOR NULO O VALOR ENTAO CONVERTEMOS PARA DBNULL.VALUE
				parameters.Add(new SQLiteParameter(columnInfo.ColumnName, value));
			}

			//LIMPAR INBSERT
			cmdColumns = cmdColumns.Remove(cmdColumns.Length - 2, 2);
			cmdParameters = cmdParameters.Remove(cmdParameters.Length - 2, 2);
			
			//PREPARAR QUERY
			var insOrRepl = executeInsertOrReplace ? "INSERT OR REPLACE " : "INSERT";
			var query = string.Format("{0} INTO {1} ({2}) VALUES ({3})", insOrRepl, mapInfo.Table.TableName, cmdColumns, cmdParameters);
			
			//EXEUCTAR DE FATO
			var result = ExecuteNonQuery(query, parameters.ToArray());
			
			if (getLastIncrement)
			{
				var columnIncrement = mapInfo.Columns.First(t => t.IsAutoIncrement);

				query = "select last_insert_rowid();";

				var obj = ExecuteScalar(query);
				var max = Convert.ToInt32(obj);

				columnIncrement.PropertyInfo.SetValue(entity, max, null);
			}

			return result;
		}


		static readonly Dictionary<string, string> DicDelete = new Dictionary<string, string>();
		public int Delete<T>(T entity) where T : class, new()
		{
			var cmdDel = new StringBuilder(); 
			var parameters = new List<SQLiteParameter>();
			var mapInfo = MapEntity(entity);
			var entityName = entity.GetType().FullName;

			var hasPrimerayKey = mapInfo.Columns.Any(t => t.IsPrimaryKey);
			var columnsInfo = hasPrimerayKey ? mapInfo.Columns.Where(t => t.IsPrimaryKey) : mapInfo.Columns;

			foreach (var columnInfo in columnsInfo)
			{
				//CRIAR OS COMANDOS EM TEXTO
				if (!DicDelete.ContainsKey(entityName))
					cmdDel.Append(string.Format("{0}=@{0}, ", columnInfo.ColumnName));
				
				//ADD OS PARAEMTROS
				var value = columnInfo.PropertyInfo.GetValue(entity, null);
				parameters.Add(new SQLiteParameter(columnInfo.ColumnName, value));
			}


			if (!DicDelete.ContainsKey(entityName))
			{
				cmdDel = cmdDel.Remove(cmdDel.Length - 2, 2);
				var query = string.Format("DELETE FROM {0} WHERE {1}", mapInfo.Table.TableName, cmdDel);

				DicDelete.Add(entityName, query);
			}

			return ExecuteNonQuery(DicDelete[entityName], parameters.ToArray());
		}


		static readonly Dictionary<string, string> DicSelect = new Dictionary<string, string>();
		public IEnumerable<T> Select<T>(int? take = null) where T : class, new()
		{
			//MAPEAR TABELA
			var entity = (T)Activator.CreateInstance(typeof(T));
			var entityName = entity.GetType().FullName;

			if (!DicSelect.ContainsKey(entityName))
			{
				var cmdSelect = new StringBuilder();
				var mapInfo = MapEntity(entity);

				//GERAR STRING DOS CAMPOS
				foreach (var columnInfo in mapInfo.Columns)
				{
					cmdSelect.Append(string.Format("{0}, ", columnInfo.ColumnName));
				}

				cmdSelect = cmdSelect.Remove(cmdSelect.Length - 2, 2);
				var query = string.Format("SELECT {0} FROM {1}", cmdSelect, mapInfo.Table.TableName);

				DicSelect.Add(entityName, query);
			}

			var limit =string.Empty;
			if (take.HasValue && take.GetValueOrDefault() > 0)
				limit = string.Format(" limit {0}", take.GetValueOrDefault());

			return ExecuteReader<T>(string.Concat(DicSelect[entityName], limit));
		}


		public int ExecuteNonQuery(string commandText, params SQLiteParameter[] parameters)
		{
			if (_connection.State == ConnectionState.Closed)
				_connection.Open();

			using (var cmd = new SQLiteCommand(_connection))
			{
				cmd.CommandText = commandText;
				cmd.Parameters.AddRange(parameters);
                cmd.Transaction = _transaction;

				var result = cmd.ExecuteNonQuery();

				//DEIXAR A CONEXAO ABERTA PARA PARTICIPAR DA TRANSAÇÃO
				if (_transaction == null)
					_connection.Close();

				return result;
			}
			
		}


		public object ExecuteScalar(string commandText, params SQLiteParameter[] parameters)
		{
			if (_connection.State == ConnectionState.Closed)
				_connection.Open();

			using (var cmd = new SQLiteCommand(_connection))
			{
				cmd.CommandText = commandText;
				cmd.Parameters.AddRange(parameters);
                cmd.Transaction = _transaction;

				var result = cmd.ExecuteScalar();

				//DEIXAR A CONEXAO ABERTA PARA PARTICIPAR DA TRANSAÇÃO
				if (_transaction == null)
					_connection.Close();

				return result;
			}

		}

		public int CleanUpDatabase()
		{
			return ExecuteNonQuery("vacuum;");
		}


        public IEnumerable<T> ExecuteReader<T>(string commandText, params SQLiteParameter[] parameters) where T : class, new()
		{
			var listResult = new HashSet<T>();

			//MAPEAR TABELA
			var entity = (T)Activator.CreateInstance(typeof(T));
			var mapInfo = MapEntity(entity);


			if (_connection.State == ConnectionState.Closed)
				_connection.Open();

			using (var cmd = new SQLiteCommand(_connection))
			{
				cmd.Parameters.AddRange(parameters);
				cmd.CommandText = commandText;
                cmd.Transaction = _transaction;

                var reader = cmd.ExecuteReader();

                //DICIONARIO POR POSIÇÃO E CMAPO
                var dicOrdinal = new Dictionary<string, int>();
                foreach (var column in mapInfo.Columns)
					dicOrdinal.Add(column.ColumnName, reader.GetOrdinal(column.ColumnName));
				
				while (reader.Read())
				{
					//OBJETO A SER CRIADO
					var currentEntity = (T) Activator.CreateInstance(typeof(T));

					//VARRER TODAS AS COLUNAS DO OBJETO ATUAL
					foreach (var columnInfo in mapInfo.Columns)
					{
						//CONVERTER O VALOR DENTRO DO READER PARA O NOVO VALOR 
						//TENTAR CONVERTER VALORES NULLABLES PODE GERAR ERRO
                        var readerValue = reader.GetValue(dicOrdinal[columnInfo.ColumnName]);
                        if (readerValue == null || readerValue == DBNull.Value) continue;

						var type = columnInfo.PropertyInfo.PropertyType;

						//SE FOR NULLABLE, ENTAO PEGAMOS O TIPONAO NULLABLE
						if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
							type = Nullable.GetUnderlyingType(columnInfo.PropertyInfo.PropertyType);

						//SO VAI TROCAR O TIPO SE OS TIPOS FOREM DIFERENTES
						var value = readerValue.GetType() != type ? Convert.ChangeType(readerValue, type) : readerValue;
						
						//ALTERAR O VALOR DA PROPRIEDADE DO OBJETO CORRENTE
						columnInfo.PropertyInfo.SetValue(currentEntity, value, null);
					}

					listResult.Add(currentEntity);
				}

			}

			//DEIXAR A CONEXAO ABERTA PARA PARTICIPAR DA TRANSAÇÃO
			if (_transaction == null)
				_connection.Close();

			return listResult;
		}


	


	    public void CreateTable<T>() where T : class, new()
		{
			var tableCreator = new TableCreator();
			var query = tableCreator.CreateSqlTable<T>();

			ExecuteNonQuery(query);
		}


		#region Transaction
		
		public void BeginTransaction()
		{
			if (_connection.State == ConnectionState.Closed)
				_connection.Open();

			_transaction = _connection.BeginTransaction();
		}

		public void RollbackTransaction()
		{
			if (_transaction == null) return;

			_transaction.Rollback();
			_transaction = null;

			if (_connection.State == ConnectionState.Open)
				_connection.Close();
		}

		public void CommitTransaction()
		{
			if (_transaction == null) return;

			_transaction.Commit();
			_transaction = null;

			if (_connection.State == ConnectionState.Open)
				_connection.Close();
		}


		#endregion


		public void Dispose()
		{
			if (_transaction != null)
				_transaction.Dispose();

			if (_connection != null)
				_connection.Dispose();
		}
	}
}
